package cn.calm.swing.osgi.persistence.impl;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import cn.calm.swing.osgi.api.ProjectPersistenceService;
import cn.calm.swing.osgi.api.annotation.Column;
import cn.calm.swing.osgi.api.annotation.Id;
import cn.calm.swing.osgi.api.annotation.MapsId;
import cn.calm.swing.osgi.api.annotation.OneToMany;
import cn.calm.swing.osgi.api.annotation.Table;
import cn.calm.swing.osgi.api.entity.Project;

public class CopyOfProjectPersistenceServiceImpl implements ProjectPersistenceService {
	private static final Set<Class<?>> simpleTypes = new HashSet<Class<?>>();
	private static final Set<Class<?>> collectionTypes = new HashSet<Class<?>>();
	private Map<Class<?>, Map<Serializable, Object>> cache = new HashMap<Class<?>, Map<Serializable, Object>>();
	private Set<Object> objs = new HashSet<Object>();
	static {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		simpleTypes.add(Integer.class);
		simpleTypes.add(int.class);
		simpleTypes.add(String.class);
		collectionTypes.add(List.class);
	}

	public CopyOfProjectPersistenceServiceImpl() {

	}

	@Override
	public List<Project> listAll() {
		List<Project> list = listByParameter(Project.class, null);
		cache.clear();
		return list;
	}

	private <E> List<E> listByParameter(Class<? extends E> clazz,
			Map<String, Serializable> para) {
		Connection con = getConnection();
		List<E> list = new ArrayList<E>();
		try {
			Statement createStatement = con.createStatement();
			Map<String, Field> simpleTypeColumns = getSimpleTypeColumnNmaes(clazz);
			Map<String, Field> mapsColumns = getMapsTypeColumnNmaes(clazz);
			Map<String, Field> collectionTypeColumns = getCollectionTypeColumnNmaes(clazz);
			// System.out.println(sb);
			ResultSet rs = createStatement.executeQuery(processSelectSql(simpleTypeColumns,para,clazz));
			while (rs.next()) {
				E pro = clazz.newInstance();
				processProperty(pro, rs,simpleTypeColumns);
				Serializable id = getId(pro);
				processCache(clazz, id, pro, mapsColumns);
				if (!collectionTypeColumns.isEmpty()) {
					for (Field f : collectionTypeColumns.values()) {
						processColProperty(f, id, pro);
					}
				}
				list.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	private<E> String processSelectSql(Map<String, Field> simpleTypeColumns,Map<String, Serializable> para,Class<? extends E> clazz){
		String table = getTableName(clazz);
		StringBuilder sb = new StringBuilder("select ");
		for (String s : simpleTypeColumns.keySet()) {
			sb.append(s);
			sb.append(" ,");
		}
		if (para != null) {
			for (String s : para.keySet()) {
				sb.append(s);
				sb.append(" ,");
			}
		}
		sb.replace(sb.length() - 1, sb.length(), " from ");
		sb.append(table);
		if (para != null && !para.isEmpty()) {
			sb.append(" where ");
			for (String s : para.keySet()) {
				sb.append(s);
				sb.append(" = '");
				sb.append(para.get(s));
				sb.append("'");
			}
		}
		return sb.toString();
	}
	private <E> void processProperty(E pro,ResultSet rs,Map<String, Field> simpleTypeColumns) throws SQLException, IllegalArgumentException, IllegalAccessException{
		ResultSetMetaData metaData = rs.getMetaData();
		int columnCount = metaData.getColumnCount();
		for (int i = 1; i < columnCount; i++) {
			String name = metaData.getColumnLabel(i).toLowerCase();
			Field field = simpleTypeColumns.get(name);
			field.setAccessible(true);
			field.set(pro, rs.getObject(i));
		}
	}
	private void processColProperty(Field f, Serializable id, Object pro)
			throws SecurityException, NoSuchFieldException,
			IllegalArgumentException, IllegalAccessException {
		OneToMany annotation = f.getAnnotation(OneToMany.class);
		String mapBy = annotation.mayBy();
		ParameterizedType genericType = (ParameterizedType) f.getGenericType();
		Class<?> type = (Class<?>) genericType.getActualTypeArguments()[0];
		Field declaredField = type.getDeclaredField(mapBy);
		MapsId annotation2 = declaredField.getAnnotation(MapsId.class);
		String column = annotation2.id();
		declaredField = type.getDeclaredField(column);
		Column annotation3 = declaredField.getAnnotation(Column.class);
		if (annotation3 == null) {
			column = declaredField.getName();
		} else {
			column = annotation3.name();
		}
		Map<String, Serializable> para1 = new HashMap<String, Serializable>();
		para1.put(column, id);
		List<Object> coll = listByParameter(type, para1);
		f.setAccessible(true);
		f.set(pro, coll);
	}

	private void processCache(Class<?> clazz, Serializable id, Object pro,
			Map<String, Field> mapsColumns) throws SecurityException,
			NoSuchFieldException, IllegalArgumentException,
			IllegalAccessException {
		Map<Serializable, Object> map = cache.get(clazz);
		if (map == null) {
			map = new HashMap<Serializable, Object>();
			cache.put(clazz, map);
		}

		Object obj = map.get(id);
		if (obj == null) {
			map.put(id, pro);
		}
		if (!mapsColumns.isEmpty()) {
			for (String s : mapsColumns.keySet()) {
				Field declaredField = clazz.getDeclaredField(s);
				declaredField.setAccessible(true);
				Object val = declaredField.get(pro);
				Field f = mapsColumns.get(s);
				Map<Serializable, Object> map2 = cache.get(f.getType());
				if (map2 != null) {
					Object object = map2.get(val);
					f.setAccessible(true);
					f.set(pro, object);
				}
			}
		}
	}

	@Override
	public void add(Project project) {
		Set<String> entityInsertSql = getEntityInsertSql(project);
		executeBatch(entityInsertSql);
		objs.clear();
	}

	private Set<String> getEntityInsertSql(Object obj) {
		Set<String> set = new HashSet<String>();
		String table = getTableName(obj.getClass());
		Map<String, Object> columns = getColumns(obj);
		StringBuilder sb = new StringBuilder("insert into ");
		sb.append(table);
		sb.append(" (");
		for (String s : columns.keySet()) {
			sb.append(s);
			sb.append(" ,");
		}
		sb.replace(sb.length() - 2, sb.length(), " )values( ");
		for (Object s : columns.values()) {
			sb.append("'");
			sb.append(s);
			sb.append("'");
			sb.append(" ,");
		}
		sb.replace(sb.length() - 2, sb.length(), " )");
		set.add(sb.toString());
		objs.add(obj);
		Map<String, Iterable<?>> collectionTypeColumns = getCollectionTypeColumns(obj);
		for (Iterable<?> o : collectionTypeColumns.values()) {
			for (Object i : o) {
				if (!objs.contains(i)) {
					set.addAll(getEntityInsertSql(i));
				}
			}
		}
		return set;
	}

	public void executeBatch(Set<String> entitySql) {
		Connection con = getConnection();
		try {
			con.setAutoCommit(false);
			Statement createStatement = con.createStatement();
			for (String s : entitySql) {
//				System.out.println(s);
				createStatement.addBatch(s);
			}
			createStatement.executeBatch();
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@Override
	public void delete(Project project) {
		Set<String> entityInsertSql = getEntityDeleteSql(project);
		executeBatch(entityInsertSql);
		objs.clear();
	}

	private Set<String> getEntityDeleteSql(Object obj) {
		Set<String> set = new HashSet<String>();
		String table = getTableName(obj.getClass());
		Map<String, Object> columns = getColumns(obj);
		StringBuilder sb = new StringBuilder("delete * from ");
		sb.append(table);
		sb.append(" where ");
		for (String s : columns.keySet()) {
			sb.append(s);
			sb.append(" ='");
			sb.append(columns.get(s));
			sb.append("' and ");
		}
		sb.replace(sb.length() - 5, sb.length(), "");
//		System.out.println(sb);
		set.add(sb.toString());
		objs.add(obj);
		Map<String, Iterable<?>> collectionTypeColumns = getCollectionTypeColumns(obj);
		for (Iterable<?> o : collectionTypeColumns.values()) {
			for (Object i : o) {
				if (!objs.contains(i)) {
					set.addAll(getEntityDeleteSql(i));
				}
			}
		}
		return set;
	}

	@Override
	public void update(Project project) {

	}

	@Override
	public Project getById(Serializable id) {
		Map<String,Serializable> para=new HashMap<String, Serializable>(); 
		Map<String, Field> simpleTypeColumnNmaes = getIdTypeColumnNmaes(Project.class);
		if(simpleTypeColumnNmaes.size()==1){
			for(String s:simpleTypeColumnNmaes.keySet()){
				para.put(s, id);
			}
		}
		List<Project> list=listByParameter(Project.class,para);
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}

	private String getTableName(Class<?> clazz) {
		Table annotation = clazz.getAnnotation(Table.class);
		if (annotation == null) {
			return clazz.getSimpleName();
		} else {
			return annotation.name();
		}
	}

	private Serializable getId(Object obj) {
		Class<?> clazz = obj.getClass();
		Field[] declaredFields = clazz.getDeclaredFields();
		for (Field f : declaredFields) {
			Id annotation = f.getAnnotation(Id.class);
			if (annotation != null) {
				f.setAccessible(true);
				try {
					return (Serializable) f.get(obj);
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	private Map<String, Field> getSimpleTypeColumnNmaes(Class<?> clazz) {
		Field[] declaredFields = clazz.getDeclaredFields();
		Map<String, Field> list = new HashMap<String, Field>();
		for (Field f : declaredFields) {
			Class<?> type = f.getType();
			if (simpleTypes.contains(type)) {
				Column annotation = f.getAnnotation(Column.class);
				String name;
				if (annotation == null) {
					name = f.getName();
				} else {
					name = annotation.name();
				}
				list.put(name.toLowerCase(), f);
			}
		}
		return list;
	}
	
	private Map<String, Field> getIdTypeColumnNmaes(Class<?> clazz) {
		Field[] declaredFields = clazz.getDeclaredFields();
		Map<String, Field> list = new HashMap<String, Field>();
		for (Field f : declaredFields) {
			Id id = f.getAnnotation(Id.class);
			if(id==null){
				continue;
			}
			Column annotation = f.getAnnotation(Column.class);
			String name;
			if (annotation == null) {
				name = f.getName();
			} else {
				name = annotation.name();
			}
			list.put(name.toLowerCase(), f);
		}
		return list;
	}
	private Map<String, Field> getMapsTypeColumnNmaes(Class<?> clazz) {
		Field[] declaredFields = clazz.getDeclaredFields();
		Map<String, Field> list = new HashMap<String, Field>();
		for (Field f : declaredFields) {
			MapsId annotation = f.getAnnotation(MapsId.class);
			if (annotation != null) {
				list.put(annotation.id(), f);
			}
		}
		return list;
	}

	private Map<String, Field> getCollectionTypeColumnNmaes(Class<?> clazz) {
		Field[] declaredFields = clazz.getDeclaredFields();
		Map<String, Field> list = new HashMap<String, Field>();
		for (Field f : declaredFields) {
			Class<?> type = f.getType();
			if (collectionTypes.contains(type)) {
				Column annotation = f.getAnnotation(Column.class);
				String name;
				if (annotation == null) {
					name = f.getName();
				} else {
					name = annotation.name();
				}
				list.put(name.toLowerCase(), f);
			}
		}
		return list;
	}

	private Map<String, Iterable<?>> getCollectionTypeColumns(Object obj) {
		Class<?> clazz = obj.getClass();
		Field[] declaredFields = clazz.getDeclaredFields();
		Map<String, Iterable<?>> map = new HashMap<String, Iterable<?>>();
		for (Field f : declaredFields) {
			Class<?> type = f.getType();
			if (collectionTypes.contains(type)) {
				Column annotation = f.getAnnotation(Column.class);
				f.setAccessible(true);
				String name;
				if (annotation == null) {
					name = f.getName();
				} else {
					name = annotation.name();
				}
				try {
					Object val = f.get(obj);
					if (val != null && val instanceof Iterable) {
						map.put(name, (Iterable<?>) val);
					}

				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		return map;
	}

	private Map<String, Object> getColumns(Object obj)
			throws IllegalArgumentException {
		Class<?> clazz = obj.getClass();
		Field[] declaredFields = clazz.getDeclaredFields();
		Map<String, Object> map = new HashMap<String, Object>();
		Map<String, Field> mapsTypeColumnNmaes = getMapsTypeColumnNmaes(clazz);
		for (Field f : declaredFields) {
			Class<?> type = f.getType();
			if (simpleTypes.contains(type)) {
				f.setAccessible(true);
				String name;
				Column annotation = f.getAnnotation(Column.class);
				if (annotation == null) {
					name = f.getName();
				} else {
					name = annotation.name();
				}
				Field field = mapsTypeColumnNmaes.get(f.getName());
				Object value = null;
				try {
					if (field != null) {
						Object temp;
						field.setAccessible(true);
						temp = field.get(obj);
						if (temp != null) {
							value = getId(temp);
						}
					} else {
						value = f.get(obj);
					}
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
				map.put(name, value);
			}
		}
		return map;
	}

	private Connection getConnection() {
		Connection con = null;
		try {
			con = DriverManager
					.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=G:\\db.mdb");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

	@Override
	public List<Project> listAllByProjectType(String type) {
		// TODO Auto-generated method stub
		return null;
	}
}
